package com.cpi.cornpp.plantversion.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.plantversion.dto.PlantVersionDTO;
import com.cpi.cornpp.plantversion.exception.PlantVersionException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

/**
 * 
 * @description: This Class is used to handle the Plant Version Data Access
 * @author Edgar Joao
 */
public class PlantVersionDAO extends DBDAO{
	/** Datasource name*/
	private static final String DATASOURCE 	= "cornpp";//$NON-NLS-1$
	private static final String SELECT_MONTH_AND_PLANT_VERSIONS = "SELECT MON.MONTH_PERIOD_VERSION_ID, plant_version_id, PLA.storage_amount, PLA.unload_amount, PLA.grind, PLA.grind_protection_days, P.SHORT_NAME FROM MONTHS_PERIODS_VERSIONS MON INNER JOIN PLANTS_VERSIONS PLA ON MON.MONTH_PERIOD_VERSION_ID = PLA.MONTH_PERIOD_VERSION_ID INNER JOIN PLANTS P ON PLA.PLANT_ID = P.PLANT_ID INNER JOIN MONTHS_PERIODS MP ON MON.MONTH_PERIOD_ID = MP.MONTH_PERIOD_ID WHERE MON.VERSION_ID = ? ORDER BY PLA.PLANT_ID, MP.START_DATE";//$NON-NLS-1$
	private static final String UPDATE_PLANT_VERSIONS = "UPDATE PLANTS_VERSIONS set storage_amount = ?, unload_amount = ?, grind = ?, grind_protection_days = ? WHERE plant_version_id = ?";//$NON-NLS-1$
	/**
	 * @author Edgar Joao
	 * @param versionId
	 * @return
	 * @throws PlantVersionException
	 */
	public List getPlantsVersions(int versionId) throws PlantVersionException{
		List listOfPlantVersions = null;
		PreparedStatement preparedStatement = null;
		PlantVersionDTO plantVersionDTO = null;
		ResultSet rs = null;
		try{				
			listOfPlantVersions = new ArrayList(0);			
			preparedStatement = prepareSQL(SELECT_MONTH_AND_PLANT_VERSIONS, DATASOURCE);
			preparedStatement.setInt(1, versionId);	
			rs = executeQuery(preparedStatement);			
			while (rs.next()){
				plantVersionDTO = new PlantVersionDTO();
				plantVersionDTO.setMonthPeriodId(rs.getInt(1));
				plantVersionDTO.setPlantVersionId(rs.getInt(2));
				plantVersionDTO.setStorageAmount(rs.getDouble(3));
				plantVersionDTO.setUnloadAmount(rs.getDouble(4));
				plantVersionDTO.setGrind(rs.getDouble(5));
				plantVersionDTO.setGrindProtectionDays(rs.getInt(6));
				plantVersionDTO.setPlantShortName(rs.getString(7));
				listOfPlantVersions.add(plantVersionDTO);
			}	
		}catch (PoolException poolException){
			PlantVersionException plantVersionException = null;
			poolException.printStackTrace(System.out);
			plantVersionException = new PlantVersionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw plantVersionException;	
		} catch (SQLException sqlException) {
			PlantVersionException plantVersionException = null;
			sqlException.printStackTrace(System.out);
			plantVersionException = new PlantVersionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw plantVersionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
		return listOfPlantVersions;		
	}
	/**
	 * @author Edgar Joao
	 * @param dto
	 * @throws PlantVersionException
	 */
	public void updatePlantsVersions(PlantVersionDTO dto) throws PlantVersionException{
		PreparedStatement preparedStatement = null;				
		try{										
			preparedStatement = prepareSQL(UPDATE_PLANT_VERSIONS, DATASOURCE);		
			preparedStatement.setDouble(1, dto.getStorageAmount());			
			preparedStatement.setDouble(2, dto.getUnloadAmount());			
			preparedStatement.setDouble(3, dto.getGrind());			
			preparedStatement.setInt(4, dto.getGrindProtectionDays());			
			preparedStatement.setInt(5, dto.getPlantVersionId());
			executeInsert(preparedStatement);						
		}catch (PoolException poolException){
			PlantVersionException plantVersionException = null;
			poolException.printStackTrace(System.out);
			plantVersionException = new PlantVersionException(poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_UPDATE);
			throw plantVersionException;	
		} catch (SQLException sqlException) {
			PlantVersionException plantVersionException = null;
			sqlException.printStackTrace(System.out);
			plantVersionException = new PlantVersionException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_UPDATE);
			throw plantVersionException;
		} finally {			
			try {				
				closeConnection(preparedStatement.getConnection());				
			} catch (SQLException sqle) {
				sqle.printStackTrace(System.out);
			}
		}
	}
}
